package com.lmc.edrive.database;

import java.net.InetAddress;
import java.net.UnknownHostException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.lmc.edrive.data.AlertData;
import com.lmc.edrive.data.Vehicle;

public class DbManager {
	private static DbManager db;
	private String sqlServer;
	private int port = 3306;
	private String dbUser = "b77cf552f46da3";
	private String dbPassword = "b4d772b1";
	// private String dbUser = "root";
	// private String dbPassword = "root";
	// jdbc:mysql://us-cdbr-cb-east-01.cleardb.net:3306/cb_tdrive
	private String cloudDbServer = "us-cdbr-cb-east-01.cleardb.net";
	private String dbpath = "cb_tdrive";

	// private String dbpath="Tdrive";
	public static synchronized DbManager getInstance() {
		if (db == null) {
			db = new DbManager();
		}

		return db;
	}

	private DbManager() {
		/*
		 * In=etAddress address = null; try { address =
		 * InetAddress.getLocalHost(); } catch (UnknownHostException e) { //
		 * TODO Auto-generated catch block e.printStackTrace(); }
		 * 
		 * sqlServer = address.getHostAddress();
		 */
		// sqlServer = "localhost";
		sqlServer = cloudDbServer;
		System.out.println("Server address  = " + sqlServer);
	}

	public String getPassword(String username) {
		String password = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbpath, dbUser, dbPassword);
			Statement statement = con.createStatement();
			String query = "select password from user where emailid=\""
					+ username + "\"";
			System.out.println("query=" + query);
			ResultSet result = statement.executeQuery(query);

			while (result.next()) {
				password = result.getString("password");

			}
			result.close();
			statement.close();
			con.close();

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return password;
	}

	public void addVehicle(String vehicleName, String milage, String mdl) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbpath, dbUser, dbPassword);
			Statement statement = con.createStatement();
			String query = "insert into user values(\"" + vehicleName + "\",\""
					+ milage + "\",\"" + mdl + "\")";
			System.out.println("query=" + query);
			statement.execute(query);
			statement.close();
			con.close();

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public String addUser(String firstName, String lastName, String emailId,
			String password, String phoneNo) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbpath, dbUser, dbPassword);
			Statement statement = con.createStatement();
			String query = "insert into user values(\"" + firstName + "\",\""
					+ lastName + "\",\"" + emailId + "\",\"" + password
					+ "\",\"" + phoneNo + "\")";
			System.out.println("query=" + query);
			statement.execute(query);
			statement.close();
			con.close();

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return password;
	}

	public void addVehicle(String emailId, String vhname, int model,
			float mileage, String type) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbpath, dbUser, dbPassword);
			Statement statement = con.createStatement();
			String query = "insert into vehicle values(\"" + emailId + "\",\""
					+ vhname + "\"," + model + "," + mileage + ",\"" + type
					+ "\")";
			System.out.println("query=" + query);
			statement.execute(query);
			statement.close();
			con.close();

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public boolean isUserExist(String emailId) {
		if (getPassword(emailId) != null)
			return true;

		return false;

	}

	public List<Vehicle> getVehicles(String emailId) {
		List<Vehicle> vehList = new ArrayList<Vehicle>();
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbpath, dbUser, dbPassword);
			Statement statement = con.createStatement();
			String query = "select * from vehicle where emailid=\"" + emailId
					+ "\"";
			System.out.println("query=" + query);
			ResultSet result = statement.executeQuery(query);

			while (result.next()) {
				Vehicle veh = new Vehicle();
				String vehicleName = result.getString("vehiclename");
				float mileage = result.getFloat("mileage");
				int model = result.getInt("model");
				String type = result.getString("type");
				veh.setVehicleName(vehicleName);
				veh.setMileage(mileage);
				veh.setModel(model);
				veh.setType(type);
				vehList.add(veh);

			}
			result.close();
			statement.close();
			con.close();

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return vehList;
	}

	public List<AlertData> getAlert(double lat1, double long1) {
		List<AlertData> alrtlist = new ArrayList<AlertData>();
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbpath, dbUser, dbPassword);
			Statement statement = con.createStatement();
			String query = "SELECT * FROM cb_tdrive.alert WHERE id IN (SELECT id from cb_tdrive.alert GROUP BY description,alertdata);";
			System.out.println("query=" + query);
			ResultSet result = statement.executeQuery(query);

			while (result.next()) {
				AlertData alrt = new AlertData();
				double lat2 = result.getDouble("latitude");
				double long2 = result.getDouble("longitude");
				String alertName = result.getString("alertdata");
				String description = result.getString("description");
				// int r= 6371 ;
				// double dlat = lat2- lat1;
				// double dlong = long2-long1;

				// double
				// a=Math.sin(dlat/2)*Math.sin(dlat/2)+Math.cos(lat1)*Math.cos(lat2)*Math.sin(dlong/2)*Math.sin(dlong/2);
				// double c=2*a*Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
				// double d=r*c;
				int R = 6371; // km
				double d = Math.acos(Math.sin(lat1) * Math.sin(lat2)
						+ Math.cos(lat1) * Math.cos(lat2)
						* Math.cos(long2 - long1))
						* R;
				System.out.println("latitude and longitude" + lat2 + ","
						+ long2);
				System.out.println("distance" + d);
				alrt.setLatitude(lat2);
				alrt.setLongitude(long2);
				alrt.setAlertName(alertName);
				alrt.setDescription(description);
				alrtlist.add(alrt);

			}
			result.close();
			statement.close();
			con.close();

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return alrtlist;
	}

	public String getFuelCost() {
		float petrolCost = 0;
		float dieselCost = 0;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbpath, dbUser, dbPassword);
			Statement statement = con.createStatement();
			String query = "select * from fuels";
			System.out.println("query=" + query);
			ResultSet result = statement.executeQuery(query);

			while (result.next()) {
				petrolCost = result.getFloat("petrolcost");
				dieselCost = result.getFloat("dieselcost");
			}
			result.close();
			statement.close();
			con.close();

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return petrolCost + "#" + dieselCost;
	}

	public void saveData(double lat1, double long1, double lat2, double long2,
			long startTime, long endTime, double distance, long totalTime) {

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbpath, dbUser, dbPassword);
			Statement statement = con.createStatement();
			String query = "insert into distance values (" + lat1 + "," + lat2
					+ "," + long1 + "," + long2 + "," + distance + ","
					+ startTime + "," + endTime + "," + totalTime + ")";
			System.out.println("query=" + query);
			statement.execute(query);
			statement.close();
			con.close();

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void saveAlert(double latitude, double longitude, long time,
			String alert, String des) {

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbpath, dbUser, dbPassword);
			Statement statement = con.createStatement();
			Date date = new Date(time);

			String query = "insert into alert(latitude,longitude,"
					+ "time,alertdata,description) values (" + latitude + ","
					+ longitude + ",'" + date.toString() + "','" + alert
					+ "','" + des + "')";

			System.out.println("query=" + query);
			statement.execute(query);
			statement.close();
			con.close();

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public long getTdriveDuration(double startLat, double startLong, double endLat,
			double endLong, long gDuration) {
		long tDuration=0;
            ResultSet result= null;
            Statement statement=null;
            Connection con =null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			 con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbpath, dbUser, dbPassword);
			 statement = con.createStatement();
			String query = "SELECT DISTINCT * FROM cb_tdrive.distance where start_lat>=" +
					startLat +
					" and end_lat<=" +
					endLat +
					"and start_long>=" +
					startLong;
			System.out.println("query=" + query);
			
			 result = statement.executeQuery(query);

			while (result.next()) {
				tDuration+= result.getLong("total_time");
				
			}
			result.close();
                    statement.close();
                    con.close();

            } catch (InstantiationException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
            } catch (IllegalAccessException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
            } catch (ClassNotFoundException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
            } catch (SQLException e) {
            	 try {
            		 if (result!=null)
					result.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
                 try { if(statement!=null)
					statement.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
                 try {if(con!=null)
					con.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
                    e.printStackTrace();
            }
            if (tDuration>0) {
                    return tDuration;
            }
                    
            return gDuration;

    }
    
    
    public void deleteVehicle(String emailid, String vehiclename) {
    	try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager
					.getConnection("jdbc:mysql://" + sqlServer + ":" + port
							+ "/" + dbpath, dbUser, dbPassword);
			Statement statement = con.createStatement();
			String query ="delete from vehicle where emailid='"+ emailid +"' and vehiclename = '" + vehiclename +"'" ;
			System.out.println("query=" + query);
			statement.execute(query);
			statement.close();
			con.close();

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }

}
